package dao;

import hashing.AeSimpleSHA1;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import model.LoginDetails;
import model.RegistrationDetails;
import model.User;
import model.UserSession;
import model.Vote;

public class UserDAO {

	public UserDAO() {

	}

	/**
	 * Given the guc-mail and password, this method returns a User model loaded
	 * with the database's content.
	 * 
	 * @param loginDetails
	 * @return
	 * @throws Exception
	 */
	public User getUserByLoginDetails(LoginDetails loginDetails) {

		return getUser("password = SHA1( CONCAT('" + loginDetails.getPassword()
				+ "', user.activation_code)) AND guc_mail = '"
				+ loginDetails.getEmail() + "';");
	}

	public User getUserByEmail(String email) {
		return getUser("guc_mail = '" + email + "'");
	}

	public User getUserBySessionId(int sessionId) {
		return getUser("session_id = " + sessionId);
	}

	public User getUserById(int id) {
		return getUser("user_id = " + id);
	}

	@SuppressWarnings("finally")
	public User getUser(String whereClause) {
		User user = null;
		try {
			Connection connection = DBConnection.getDefaultConnection();
			Statement statement = connection.createStatement();
			String query = "SELECT * FROM user WHERE " + whereClause;
			System.out.println(query);
			ResultSet rs = statement.executeQuery(query);
			user = loadUser(rs);
			statement.close();
			DBConnection.closeConnection(connection);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			return user;
		}
	}

	/**
	 * Returns a list of votes, sorted from the latest to the oldest vote.
	 * 
	 * @param <code>num</code> is an optional parameter, if you set it to 20, it
	 *        will get you the last 20 votes, not including this parameter or
	 *        setting it to <code>-1</code> returns all the votes
	 */
	@SuppressWarnings("finally")
	public List<Vote> latestVotes(int num) {

		List<Vote> votesList = new ArrayList<Vote>();

		try {
			Connection connection = DBConnection.getDefaultConnection();
			Statement statement = connection.createStatement();

			String query = "SELECT name, vote, vote_time FROM user ORDER BY vote_time";
			if (num >= 0) {
				query += " Limit " + num;
			}

			ResultSet rs = statement.executeQuery(query);

			while (rs.next()) {
				Vote vote = new Vote();
				vote.setVoterName(rs.getString("name"));
				vote.setEncryptedVote(rs.getString("vote"));
				vote.setVoteTime(rs.getDate("vote_time"));
				if (vote.getEncryptedVote() != null) {
					votesList.add(vote);
				}
			}
			rs.close();
			statement.close();
			DBConnection.closeConnection(connection);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			return votesList;
		}
	}

	/**
	 * Returns a list of votes, sorted from the latest to the oldest vote.
	 */
	public List<Vote> latestVotes() throws Exception {
		return latestVotes(-1);
	}

	/**
	 * * Registers the user into the database, given the registration details.
	 * SHA1 hashing the password and the current timestamp. The later acts as
	 * the activation code
	 * 
	 * @param registrationDetails
	 * @return the activation code of this registration process
	 */
	@SuppressWarnings("finally")
	public String register(RegistrationDetails registrationDetails) {

		String activationCode = "";
		try {
			Connection connection = DBConnection.getDefaultConnection();
			Statement statement = connection.createStatement();

			activationCode = AeSimpleSHA1.SHA1(new Date().toString());

			String query = "INSERT INTO user (guc_mail, password, name, activation_code) VALUES ('"
					+ registrationDetails.getEmail()
					+ "', SHA1('"
					+ registrationDetails.getPassword()
					+ activationCode
					+ "'), '"
					+ registrationDetails.getFullName()
					+ "', '"
					+ activationCode + "');";
			// System.out.println(query);
			statement.executeUpdate(query);
			statement.close();
			DBConnection.closeConnection(connection);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			return activationCode;
		}
	}

	public void commitUser(User user) {
		try {
			Connection connection = DBConnection.getDefaultConnection();
			Statement statement = connection.createStatement();

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			String query = "UPDATE user SET guc_mail = '"
					+ user.getGucMail()
					+ "', name = '"
					+ user.getName()
					+ (user.getVote() != null ? "', vote = '" + user.getVote()
							: "")
					+ (user.getVoteTime() != null ? "', vote_time = '"
							+ sdf.format(user.getVoteTime()) : "")
					+ (user.getActivationCode() != null ? "', activation_code = '"
							+ user.getActivationCode()
							: "")
					+ "', is_activated = '"
					+ (user.isActivated() ? 1 : 0)
					+ "'"
					+ (user.getSession() != null ? ", session_id = '"
							+ user.getSession().getSessionID()
							+ "', session_expiry_date = '"
							+ sdf.format(user.getSession().getExpiryDate())
							+ "'" : "") + " WHERE user_id = '" + user.getId()
					+ "';";

			statement.executeUpdate(query);
			statement.close();
			DBConnection.closeConnection(connection);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// return activationCode;
		}
	}

	@SuppressWarnings("finally")
	public User loadUser(ResultSet rs) {
		User user = null;
		try {
			if (rs.next()) {
				user = new User();
				user.setId(rs.getInt("user_id"));
				user.setGucMail(rs.getString("guc_mail"));
				user.setAdmin(rs.getInt("is_admin") == 1 ? true : false);
				user.setHashedPassword(rs.getString("password"));
				user.setName(rs.getString("name"));
				user
						.setActivated(rs.getInt("is_activated") == 1 ? true
								: false);
				user.setVote(rs.getString("vote"));
				user.setVoteTime(rs.getDate("vote_time"));
				user.setActivationCode(rs.getString("activation_code"));
				UserSession session = new UserSession();
				session.setSessionID(rs.getInt("session_id"));
				session.setExpiryDate(rs.getDate("session_expiry_date"));
			} else {
				// throw new
				// Exception("Wrong Username and Password combination!");
				return null;
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			return user;
		}
	}

	public int getNumberOfUsers() {
		int numberOfUsers = -1;
		try {
			Connection connection = DBConnection.getDefaultConnection();
			Statement statement = connection.createStatement();
			String query = "SELECT count(*) AS Num FROM user";
			ResultSet rs = statement.executeQuery(query);
			rs.next();
			numberOfUsers = rs.getInt("Num");
			rs.close();
			statement.close();
			DBConnection.closeConnection(connection);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			return numberOfUsers;
		}
	}
	
	public void resetAllVotes(){
		try {
			Connection connection = DBConnection.getDefaultConnection();
			Statement statement = connection.createStatement();

			String query = "UPDATE user SET `vote` = NULL, `vote_time` = NULL";

			statement.executeUpdate(query);
			statement.close();
			DBConnection.closeConnection(connection);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// return activationCode;
		}
	}
	/*
	 * public void vote(int vote, User user){ Date voteTime = new Date(); try{
	 * Connection connection = DBConnection.getDefaultConnection(); Statement
	 * statement = connection.createStatement();
	 * 
	 * SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	 * user.setVote(vote+""); user.setVoteTime(voteTime); String query =
	 * "UPDATE user SET vote = '"
	 * +vote+"', vote_time = '"+sdf.format(voteTime)+"' WHERE user_id = "
	 * +user.getId()+";"; }catch(Exception e){ e.printStackTrace(); } }
	 */
}
